package com.c1g19.dao.illness;

import com.c1g19.dao.BaseDao;
import com.c1g19.utils.BasicRowProcessorFix;
import com.c1g19.utils.DbControl;
import com.c1g19.utils.JsonUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.json.JSONArray;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.HashMap;
import java.util.Map;

/**
 * 该类为病例类Dao层，用以查询与病例相关的内容
 * Created by Mr_C on 2017/3/7.
 */
public class CaseDao extends BaseDao {

    private static final Logger logger = Logger.getLogger(CaseDao.class);

    /**
     * 得到所有的主病例类
     *
     * @return 所有主病例类的MapList
     */
    public Map<String, Object> getAll_Main_Case() {
        logger.info("执行 getAll_Main_Case");
        Map<String, Object> dataMap = new HashMap<String, Object>();
        List<Map<String, Object>> maincaseMapList = new ArrayList<Map<String, Object>>(); //病例大类的list
        long totalCount = 0; // 病例大类的总数
        Connection conn = null;
        String sql = "";
        try {
            conn = DbControl.getConnection();

            sql = "select count(main_case_id) from main_case where display>0";
            totalCount = getQueryRunner().query(conn, sql,
                    new ScalarHandler<Long>());
            sql = "select * from main_case where display>0;";
            maincaseMapList = getQueryRunner().query(conn, sql,
                    new MapListHandler(new BasicRowProcessorFix()));
            logger.info("查询到的总病例大类共有" + maincaseMapList.size() + "类");
        } catch (Exception e) {
            logger.error("", e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        dataMap.put("totalCount", totalCount);
        dataMap.put("dataList", maincaseMapList);
        return dataMap;
    }

    /**
     * 通过ID获得主病例下的全部病例
     *
     * @param main_case_id
     * @return
     */
    public Map<String, Object> getCaseWithID(int main_case_id, String filter, int beginIndex, int pageSize) {
        logger.info("执行 getCaseWithID");
        Map<String, Object> dataMap = new HashMap<String, Object>();
        List<Map<String, Object>> caseMapList = new ArrayList<Map<String, Object>>(); //存放病例的List
        Connection conn = null;
        long totalCount = 0;
        String sql = "";
        try {
            conn = DbControl.getConnection();
            sql = "select count(caselist_id) from caselist where main_case_id = ? and display>0 "
                    + filter;
            totalCount = getQueryRunner().query(conn, sql,
                    new ScalarHandler<Long>(), main_case_id);

            if (pageSize > 0) {
                sql = "select caselist_id, main_case_id, case_name_dis from caselist where main_case_id = ? and display>0 "
                        + filter + " order by caselist_id desc limit ?,?";
                caseMapList = getQueryRunner().query(conn, sql,
                        new MapListHandler(new BasicRowProcessorFix()), main_case_id, beginIndex, pageSize);
                logger.info("查询到的指定病例大类下共有" + caseMapList.size() + "小类");
            } else {
                sql = "select caselist_id, main_case_id, case_name_dis from caselist where main_case_id = ? and display>0"
                        + filter + " order by caselist_id desc";
                caseMapList = getQueryRunner().query(conn, sql,
                        new MapListHandler(new BasicRowProcessorFix()), main_case_id);
                logger.info("查询到的指定病例大类下共有" + caseMapList.size() + "小类");
            }
        } catch (Exception e) {
            logger.error("", e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        dataMap.put("totalCount", totalCount);
        dataMap.put("dataList", caseMapList);
        return dataMap;
    }

    /**
     * 通过case_id获得相关病例的详细信息
     *
     * @param case_id 病例独有的ID号
     * @return 病例详细信息的Map
     */
    public Map<String, Object> getCaseDetail(int case_id) {
        logger.info("执行 getCaseDetail");
        Map<String, Object> caseDetailMap = null;
        Connection conn = null;
        try {
            conn = DbControl.getConnection();
            String sql = "select * from caselist where caselist_id = ?";
            caseDetailMap = getQueryRunner().query(conn, sql,
                    new MapHandler(new BasicRowProcessorFix()), case_id);

            //查询结果判断
            if (caseDetailMap.size() > 0)
                logger.info("已查询到相关信息");
            else
                logger.info("未查到相关信息或查询信息出错");

        } catch (Exception e) {
            logger.error("", e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        String loc = "";
        List<String> loclist = null;
        //对得到的location进行切分,遍历key找到带_loc的字段

        Map<String, Object> tempCaseDetailMap = new HashMap<String, Object>();

        for (String key : caseDetailMap.keySet()) {
            if (key.contains("_loc")) {
                loc = caseDetailMap.get(key).toString();
                logger.info("地址集合为:" + loc);
                if (loc != null) {
                    loclist = spitLocation(loc);
                    caseDetailMap.replace(key, loclist);
                    logger.info(key+":"+loclist.size());
                    tempCaseDetailMap.put(key + "_count", loclist.size());
//                    logger.info(caseDetailMap.get(key));
                }
            }
        }

        for (String key : tempCaseDetailMap.keySet()) {
            caseDetailMap.put(key, tempCaseDetailMap.get(key));
        }

        return caseDetailMap;
    }


    /**
     * 获得所有的岗位
     *
     * @return
     */
    public Map<String, Object> getAllProfession() {
        logger.info("执行 getAllProfession");
        Map<String, Object> dataMap = new HashMap<String, Object>();
        List<Map<String, Object>> professionMapList = new ArrayList<Map<String, Object>>(); //职业大类的list
        long totalCount = 0; // 职业大类的总数
        Connection conn = null;
        String sql = "";
        try {
            conn = DbControl.getConnection();

            sql = "select count(profession_id) from profession where display>0";
            totalCount = getQueryRunner().query(conn, sql,
                    new ScalarHandler<Long>());
            sql = "select * from profession where display>0;";
            professionMapList = getQueryRunner().query(conn, sql,
                    new MapListHandler(new BasicRowProcessorFix()));
            logger.info("查询到的总职业共有" + professionMapList.size() + "个");
        } catch (Exception e) {
            logger.error("", e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        dataMap.put("totalCount", totalCount);
        dataMap.put("dataList", professionMapList);
        return dataMap;
    }

    /**
     * 通过pro_id 获得相关职位的详细信息
     *
     * @param pro_id 职位独有的ID号
     * @return 职位详细信息的Map
     */
    public Map<String, Object> getProfessionDetail(int pro_id) {
        logger.info("执行 getProfessionDetail");
        Map<String, Object> proDetailMap = null;
        List<Map<String, Object>> tempproDetailMap = null;
        List<Map<String, Object>> tempflowDetailMap = null;
        Connection conn = null;
        String sql = "";
        try {
            conn = DbControl.getConnection();
            sql = "select * from profession where profession_id = ?";
            proDetailMap = getQueryRunner().query(conn, sql,
                    new MapHandler(new BasicRowProcessorFix()), pro_id);

            String pro_name = proDetailMap.get("pro_name").toString();
            sql = "select office_id, office_name from office where relevant_person like ?";
            tempproDetailMap = getQueryRunner().query(conn, sql,
                    new MapListHandler(new BasicRowProcessorFix()), '%' + pro_name + '%');

            if (tempproDetailMap.size() > 0)
                logger.info("已查询该职业相关诊室");
            else
                logger.info("未查到该职业相关诊室");

            String res = JsonUtils.listO2Json(tempproDetailMap);
            logger.info("res" + res);

            proDetailMap.put("relevant_office", res);
            proDetailMap.put("relevant_office_count", tempproDetailMap.size());

        } catch (Exception e) {
            logger.error("", e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        String loc = "";
        List<String> loclist = null;
        //对得到的location进行切分,遍历key找到带_loc的字段

        Map<String, Object> tempProDetailMap = new HashMap<String, Object>();
        String idlist = "";
        for (String key : proDetailMap.keySet()) {
            if (key.equals("flow_id_list")) {
                loc = proDetailMap.get(key).toString();
                logger.info("流程集合为:" + loc);
                if (loc != null) {
                    loclist = spitLocation(loc);
                    idlist = loclist.toString();
                    idlist = idlist.replace('[', '(');
                    idlist = idlist.replace(']', ')');
                    proDetailMap.replace(key, loclist);
                    tempProDetailMap.put(key + "_count", loclist.size());
//                    logger.info(caseDetailMap.get(key));
                }
            }
        }

        for (String key : tempProDetailMap.keySet()) {
            proDetailMap.put(key, tempProDetailMap.get(key));
        }

        try {
            conn = DbControl.getConnection();
            logger.info("idlist:" + idlist.length());
            if (idlist.length() > 2) {
                sql = "select * from flow where display > 0 and flow_id in " + idlist;
                tempflowDetailMap = getQueryRunner().query(conn, sql,
                        new MapListHandler(new BasicRowProcessorFix()));

                if (tempflowDetailMap.size() > 0)
                    logger.info("已查询到该职业相关流程");
                else
                    logger.info("未查到该职业相关流程");

                String res = JsonUtils.listO2Json(tempflowDetailMap);
                logger.info("res" + res);

                proDetailMap.put("flow_detail", res);
                proDetailMap.put("flow_detail_count", tempflowDetailMap.size());
            } else {
                proDetailMap.put("flow_detail", "[]");
                proDetailMap.put("flow_detail_count", 0);
            }

        } catch (Exception e) {
            logger.error("", e);
        } finally {
            DbUtils.closeQuietly(conn);
        }

        return proDetailMap;
    }

    /**
     * 通过flow_id 获得相关职位的详细信息
     *
     * @param flow_id 职位独有的ID号
     * @return 职位详细信息的Map
     */
    public Map<String, Object> getFlowDetail(int flow_id) {
        logger.info("执行 getFlowDetail");
        Map<String, Object> flowDetailMap = null;
        Map<String, Object> flowblockDetailMap = new HashMap<String,Object>();
        List<Map<String, Object>> tempflowDetailMap = null;

        Connection conn = null;
        String sql = "";
        String flow_blocklist = "";
        try {
            conn = DbControl.getConnection();
            sql = "select flow_blocklist from flow where flow_id = ?";
            flowDetailMap = getQueryRunner().query(conn, sql,
                    new MapHandler(new BasicRowProcessorFix()), flow_id);
            flow_blocklist = flowDetailMap.get("flow_blocklist").toString();
            logger.info("子流程集合为:" + flow_blocklist);
            List<String> loclist = null;
            String idlist = "";
            if (flow_blocklist != null) {
                loclist = spitLocation(flow_blocklist);
                idlist = loclist.toString();
                idlist = idlist.replace('[', '(');
                idlist = idlist.replace(']', ')');
                if (idlist.length() > 2) {
                    sql = "select * from flow_block where flow_block_id in " + idlist;
                    tempflowDetailMap = getQueryRunner().query(conn, sql,
                            new MapListHandler(new BasicRowProcessorFix()));

                    if (tempflowDetailMap.size() > 0)
                        logger.info("已查询到该职业相关子流程");
                    else
                        logger.info("未查到该职业相关子流程");

                    for(int i=0; i<tempflowDetailMap.size();i++){
                        String block_img_loc = (String) tempflowDetailMap.get(i).get("block_img_loc");
                        List<String> flowloclist = spitLocation(block_img_loc);
                        JSONArray array = new JSONArray(flowloclist);

                        tempflowDetailMap.get(i).replace("block_img_loc",array);
                        tempflowDetailMap.get(i).put("block_img_loc_count",flowloclist.size());

                        String block_video_loc = (String) tempflowDetailMap.get(i).get("block_vio_loc");
                        flowloclist = spitLocation(block_video_loc);
                        array = new JSONArray(flowloclist);

                        tempflowDetailMap.get(i).replace("block_vio_loc",array);
                        tempflowDetailMap.get(i).put("block_vio_loc_count",flowloclist.size());

                        String block_viothb_loc = (String) tempflowDetailMap.get(i).get("block_viothb_loc");
                        flowloclist = spitLocation(block_viothb_loc);
                        array = new JSONArray(flowloclist);

                        tempflowDetailMap.get(i).replace("block_viothb_loc",array);
                        tempflowDetailMap.get(i).put("block_viothb_loc_count",flowloclist.size());
                    }

                    String res = JsonUtils.listO2Json(tempflowDetailMap);
                    logger.info("res" + res);

                    flowblockDetailMap.put("flow_detail", res);
                    flowblockDetailMap.put("flow_detail_count", tempflowDetailMap.size());
                } else {
                    flowblockDetailMap.put("flow_detail", "");
                    flowblockDetailMap.put("flow_detail_count", 0);
                }

            }
        } catch (Exception e) {
            logger.error("", e);
        } finally {
            DbUtils.closeQuietly(conn);
        }
        return flowblockDetailMap;
    }


    /**
     * 切分混在一起的地址字符串
     *
     * @param loc 地址
     * @return 地址的String数组
     */
    public List<String> spitLocation(String loc) {

        String[] locationlist = loc.split(";");
        List<String> loclist = new ArrayList<String>();
//        logger.info("切分后得到地址：");
        for (int i = 0; i < locationlist.length; i++) {
//            logger.info(locationlist[i]);
            if(!locationlist[i].equals(""))
                loclist.add(locationlist[i]);
        }
        return loclist;
    }
}
